package Colecciones;

import Modelo.EjecutaConsulta;
import Modelo.Fila;
import java.io.PrintStream;
import java.util.Vector;

public class Empresa
{
  public static Vector getContactosPorEmpresa(String p_ext_org_id, int p_reg)
  {
    Vector datos = new Vector();
    try {
      String consulta = "SELECT   EMP.EXT_ORG_ID,  EFFDT,  EFF_STATUS,  OTH_NAME_SORT_SRCH,  LOC.DESCR,  EMPLID,  CONTACT_NAME  FROM   PS_EXT_ORG_TBL EMP,  PS_ORG_LOCATION LOC,  PS_ORG_CONTACT CONT WHERE  EMP.EXT_ORG_ID = LOC.EXT_ORG_ID  AND EMP.EXT_ORG_ID = CONT.EXT_ORG_ID   AND EMP.EXT_ORG_ID  LIKE '%" + p_ext_org_id + "%' ";

      if (p_reg > 0)
        consulta = consulta + " AND ROWNUM <= " + String.valueOf(p_reg);

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Consultas.getContactosPorEmpresa " + e.toString());
    }
    return datos;
  }

  public static Vector getEmpresasPorCiudad(String p_city, int p_reg)
  {
    Vector datos = new Vector();
    try {
      String consulta = " SELECT  EMP.EXT_ORG_ID,  EFFDT,  EFF_STATUS,  DESCR NOMBRE,  LOC.DESCR,  LOC.CITY FROM  PS_EXT_ORG_TBL EMP,  PS_ORG_LOCATION LOC WHERE  EMP.EXT_ORG_ID = LOC.EXT_ORG_ID  AND   LOC.CITY = '" + p_city + "' ";

      if (p_reg > 0)
        consulta = consulta + " AND ROWNUM <= " + String.valueOf(p_reg);

      consulta = consulta + "  ORDER BY DESCR";
      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Consultas.getEmpresasPorCiudad " + e.toString());
    }
    return datos;
  }

  public static Vector getEmpresasPorCiudad2(String p_ext_org_id, int p_reg)
  {
    Vector datos = new Vector();
    try {
      String consulta = " SELECT  EMP.EXT_ORG_ID,  EFFDT,  EFF_STATUS,  DESCR NOMBRE,  LOC.DESCR,  LOC.CITY FROM  PS_EXT_ORG_TBL EMP,  PS_ORG_LOCATION LOC WHERE  EMP.EXT_ORG_ID = LOC.EXT_ORG_ID  AND   LOC.EXT_ORG_ID = '" + p_ext_org_id + "' ";

      if (p_reg > 0)
        consulta = consulta + " AND ROWNUM <= " + String.valueOf(p_reg);

      consulta = consulta + "  ORDER BY DESCR";
      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Consultas.getEmpresasPorCiudad2 " + e.toString());
    }
    return datos;
  }

  public static Vector listadoEmpresas(int p_reg)
  {
    Vector datos = new Vector();
    try {
      String consulta = " SELECT  EMP.EXT_ORG_ID CONSECUTIVO,  TRANSLATE(EMP.DESCR, '\"', '`')  NOMBRE,  EMP.EXT_ORG_TYPE TIPO_EMPRESA,  PHN.PHONE TELEFONO,   '' EXTENSION,   TRANSLATE(LOC.DESCR, '\"', '`') DIRECCION,   LOC.COUNTRY PAIS,  LOC.STATE DEP_CONSECUTIVO,   LOC.CITY CIU_CONSECUTIVO,   LOC.EMAILID CORREO  FROM  PS_EXT_ORG_TBL EMP  LEFT JOIN PS_ORG_LOCATION LOC ON EMP.EXT_ORG_ID = LOC.EXT_ORG_ID  LEFT JOIN PS_ORG_LOC_PHONES PHN ON EMP.EXT_ORG_ID = PHN.EXT_ORG_ID";

      if (p_reg > 0)
        consulta = consulta + " WHERE ROWNUM <= " + String.valueOf(p_reg);

      consulta = consulta + "  ORDER BY EMP.DESCR ";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Empresa.listadoEmpresas " + e.toString());
    }
    return datos;
  }

  public static Vector listadoEmpresas()
  {
    return listadoEmpresas(0);
  }

  public static Vector consultarEmpresas(String strNombreEmpresa, int p_reg)
  {
    Vector datos = new Vector();
    try {
      String consulta = "SELECT  EMP.EXT_ORG_ID CONSECUTIVO,  TRANSLATE(EMP.DESCR, '\"', '`')  NOMBRE,  EMP.EXT_ORG_TYPE TIPO_EMPRESA,  PHN.PHONE TELEFONO,   '' EXTENSION,   TRANSLATE(LOC.DESCR, '\"', '`') DIRECCION,   LOC.COUNTRY PAIS,  LOC.STATE DEP_CONSECUTIVO,   LOC.CITY CIU_CONSECUTIVO,   LOC.EMAILID CORREO  FROM  PS_EXT_ORG_TBL EMP  LEFT JOIN PS_ORG_LOCATION LOC ON EMP.EXT_ORG_ID = LOC.EXT_ORG_ID  LEFT JOIN PS_ORG_LOC_PHONES PHN ON EMP.EXT_ORG_ID = PHN.EXT_ORG_ID " +
              "WHERE LOWER(EMP.DESCR) LIKE LOWER('"+strNombreEmpresa+"%')";

      if (p_reg > 0)
        consulta = consulta + " AND ROWNUM <= " + String.valueOf(p_reg);

      consulta = consulta + " ORDER BY EMP.DESCR ";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Empresa.listadoEmpresas " + e.toString());
    }
    return datos;
  }

  public static Vector contactosPorEmpresa(String ext_org_id)
  {
    Vector datos = new Vector();
    try {
      String consulta = " SELECT   CON.EXT_ORG_ID CONSECUTIVO,   CON.CONTACT_NAME NOMBRE,   PHN.PHONE TELEFONO,   PHN.PHONE_TYPE TIPO_TELEFONO,   EMP.ADDRESS1 DIRECCION FROM   PS_EXT_ORG_TBL EMP,   PS_ORG_CONTACT CON,   PS_ORG_CNTCT_PHN PHN WHERE   EMP.EXT_ORG_ID = '" + ext_org_id + "' " + "   AND con.ext_org_id = EMP.EXT_ORG_ID" + "   AND phn.ext_org_id = EMP.EXT_ORG_ID";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Empresa.contactosPorEmpresa " + e.toString());
    }
    return datos;
  }

  static String getNewExtOrgId() {
    throw new UnsupportedOperationException("Not yet implemented");
  }

  public static Vector listadoTiposEmpresa(int p_reg)
  {
    Vector datos = new Vector();
    try {
      String consulta = "SELECT CODIGO AS CONSECUTIVO, NOMBRE FROM TIPOS_ACTIVIDAD";

      if (p_reg > 0)
        consulta = consulta + " WHERE ROWNUM <= " + String.valueOf(p_reg);

      consulta = consulta + " ORDER BY NOMBRE";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Empresa.listadoEmpresas " + e.toString());
    }
    return datos;
  }

  public static String obtenerNombreTipoEmpresa(String strCodigoActividad) {
    Vector datos = new Vector();
    String strNombre = new String();
    try {
      String consulta = "SELECT NOMBRE FROM TIPOS_ACTIVIDAD WHERE CODIGO = '"+strCodigoActividad+"'";
      datos = EjecutaConsulta.Ejecutar(consulta);

      if (datos.size() > 0) {
          Fila filaTMP = (Fila) datos.firstElement();
          strNombre = filaTMP.getValor("NOMBRE");
      }
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Empresa.obtenerNombreTipoEmpresa " + e.toString());
    }
    return strNombre;
  }

  public static Vector listadoTiposEmpresa()
  {
    return listadoTiposEmpresa(0);
  }

  public static Vector buscarEmpresa(String nombre, String ciudad)
  {
    Vector datos = new Vector();
    try {
      String consulta = " SELECT  EMP.EXT_ORG_ID CONSECUTIVO,  EMP.DESCR NOMBRE,  EMP.EXT_ORG_TYPE TIPO_EMPRESA,  EMP.CITY CIUDAD,  PHN.PHONE TELEFONO,   '' EXTENSION,   LOC.DESCR DIRECCION  FROM  PS_EXT_ORG_TBL EMP,  PS_ORG_LOCATION LOC,  PS_ORG_LOC_PHONES PHN WHERE  EMP.EXT_ORG_ID = LOC.EXT_ORG_ID AND  EMP.EXT_ORG_ID = PHN.EXT_ORG_ID AND  EMP.OTH_NAME_SORT_SRCH LIKE '%" + nombre + "%' AND" + "  EMP.CITY CIUDAD '%" + ciudad + "%'";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Empresa.buscarEmpresa " + e.toString());
    }
    return datos;
  }

  public static Vector buscarEmpresa(String extOrgId)
  {
    Vector datos = new Vector();
    try {
      //String consulta = " SELECT  EMP.EXT_ORG_ID CONSECUTIVO,  EMP.OTH_NAME_SORT_SRCH NOMBRE,  EMP.EXT_ORG_TYPE TIPO_EMPRESA,  EMP.CITY CIUDAD,  PHN.PHONE TELEFONO,   '' EXTENSION,   LOC.DESCR DIRECCION  FROM  PS_EXT_ORG_TBL EMP,  PS_ORG_LOCATION LOC,  PS_ORG_LOC_PHONES PHN WHERE  EMP.EXT_ORG_ID = LOC.EXT_ORG_ID AND  EMP.EXT_ORG_ID = PHN.EXT_ORG_ID AND  EMP.EXT_ORG_ID = " + extOrgId;
      String consulta = "SELECT EMP.EXT_ORG_ID CONSECUTIVO, TRANSLATE(EMP.DESCR, '\"', '`') AS NOMBRE, EMP.EXT_ORG_TYPE TIPO_EMPRESA, PHN.PHONE TELEFONO, '' EXTENSION, TRANSLATE(EMP.ADDRESS1, '\"', '`') DIRECCION, LOC.COUNTRY PAIS, LOC.STATE DEP_CONSECUTIVO, LOC.CITY CIU_CONSECUTIVO, LOC.EMAILID CORREO FROM PS_EXT_ORG_TBL EMP  LEFT JOIN PS_ORG_LOCATION LOC ON EMP.EXT_ORG_ID = LOC.EXT_ORG_ID  LEFT JOIN PS_ORG_LOC_PHONES PHN ON EMP.EXT_ORG_ID = PHN.EXT_ORG_ID WHERE EMP.EXT_ORG_ID = '"+extOrgId+"'";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Empresa.buscarEmpresa " + e.toString());
    }
    return datos;
  }

  public static String obtenerConsecutivoEmpresa(String strNombreEmpresa)
  {
    String strConsecutivo = "";

    String strTablas = " FROM SYSADM.PS_EXT_ORG_TBL";
    try
    {
      String strSql = "SELECT EXT_ORG_ID" + strTablas + " WHERE LOWER(DESCR) = LOWER('" + strNombreEmpresa + "')";

      Vector vResultado = EjecutaConsulta.Ejecutar(strSql);
      if (vResultado.size() > 0)
        strConsecutivo = ((Fila)vResultado.get(0)).getValor("EXT_ORG_ID");
    } catch (Exception e) {
      e.printStackTrace();
    }

    return strConsecutivo;
  }

  public static String obtenerConsecutivoCargo(String strNombreCargo)
  {
    String strConsecutivo = "";
    try
    {
      String strSql = "SELECT US_SOC_CD FROM PS_US_SOC_TBL WHERE LOWER(DESCR) = LOWER('" + strNombreCargo + "')";

      Vector vResultado = EjecutaConsulta.Ejecutar(strSql);
      if (vResultado.size() > 0)
        strConsecutivo = ((Fila)vResultado.get(0)).getValor("US_SOC_CD");
    } catch (Exception e) {
      e.printStackTrace();
    }

    return strConsecutivo;
  }

  public static String obtenerNombreEmpresa(String strConsecutivoEmpresa)
  {
    String strConsecutivo = "";

    String strTablas = " FROM SYSADM.PS_EXT_ORG_TBL";
    try
    {
      String strSql = "SELECT DESCR" + strTablas + " WHERE EXT_ORG_ID = '" + strConsecutivoEmpresa + "'";

      Vector vResultado = EjecutaConsulta.Ejecutar(strSql);

      if (vResultado.size() > 0)
        strConsecutivo = ((Fila)vResultado.get(0)).getValor("DESCR");
    } catch (Exception e) {
      e.printStackTrace();
    }

    return strConsecutivo;
  }

  public static String obtenerNombreCargo(String strConsecutivoCargo)
  {
    String strConsecutivo = "";
    try
    {
      String strSql = "SELECT DESCR FROM PS_US_SOC_TBL WHERE US_SOC_CD = '" + strConsecutivoCargo + "'";

      Vector vResultado = EjecutaConsulta.Ejecutar(strSql);

      if (vResultado.size() > 0)
        strConsecutivo = ((Fila)vResultado.get(0)).getValor("DESCR");
    } catch (Exception e) {
      e.printStackTrace();
    }

    return strConsecutivo;
  }
}